import Router from '@koa/router';
import sequelize from '../config/db.js';
const router = new Router({ prefix: '/api' });
import { unserialize } from 'php-unserialize';

const WP_BASE_URL = 'http://localhost/myBlog';

// 统一的错误响应结构
const errorResponse = (code, message) => ({
  code,
  message,
  data: null
});

// 统一的成功响应结构
const successResponse = (data, meta = null) => ({
  code: 'SUCCESS',
  message: '操作成功',
  data,
  meta
});

const fetchWordPressData = async () => {
  // 2. 获取标签映射
  const [tags] = await sequelize.query(`
    SELECT term_id AS id, name 
    FROM wp_blog_terms 
    JOIN wp_blog_term_taxonomy USING(term_id) 
    WHERE taxonomy = 'post_tag'
  `);
  const tagMap = new Map(tags.map(t => [t.id, t.name]));
  // 1. 获取分类
  const [categories] = await sequelize.query(`
    SELECT term_id AS id, name, COUNT(object_id) AS count, taxonomy
    FROM wp_blog_terms
    JOIN wp_blog_term_taxonomy USING(term_id)
    LEFT JOIN wp_blog_term_relationships USING(term_taxonomy_id)
    WHERE taxonomy = 'category' AND name != '未分类'
    GROUP BY term_id, taxonomy;
    `);


  // 3. 按分类获取文章 
  const groupedData = [];
  for (const category of categories) {
    if (category.count > 0) {
      const [posts] = await sequelize.query(`
            SELECT 
    p.ID AS id,
    p.post_title AS title,
    p.post_date AS date,
    COALESCE(l.like_count, 0) AS like_count,
    COALESCE(c.comment_count, 0) AS comment_count,
    LEAST(
        ROUND(
            COALESCE(a_stats.view_count, 0) * 0.7 + 
            COALESCE(l.like_count, 0) * 0.5 + 
            COALESCE(c.comment_count, 0) * 0.3
        ) * 100,
        5000
    ) AS hot_score,
    GROUP_CONCAT(DISTINCT tr_tag.term_taxonomy_id) AS tags,
    thumb_meta.meta_value AS image_id,
    img_post.guid AS image
FROM wp_blog_posts p
-- 分类关联
JOIN wp_blog_term_relationships tr ON p.ID = tr.object_id
JOIN wp_blog_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
-- 点赞统计
LEFT JOIN (
    SELECT target_id, COUNT(*) AS like_count 
    FROM likes 
    WHERE target_type = 'posts'
    GROUP BY target_id
) l ON p.ID = l.target_id
-- 评论统计
LEFT JOIN (
    SELECT article_id, COUNT(*) AS comment_count 
    FROM comments 
    WHERE is_deleted = 0
    GROUP BY article_id
) c ON p.ID = c.article_id
-- 浏览统计
LEFT JOIN articlestats a_stats ON p.ID = a_stats.article_id
-- 标签关联
LEFT JOIN wp_blog_term_relationships tr_tag ON p.ID = tr_tag.object_id
LEFT JOIN wp_blog_term_taxonomy tt_tag ON tr_tag.term_taxonomy_id = tt_tag.term_taxonomy_id
    AND tt_tag.taxonomy = 'post_tag'
-- 特色图片获取（优化后的方式）
LEFT JOIN wp_blog_postmeta thumb_meta ON p.ID = thumb_meta.post_id 
    AND thumb_meta.meta_key = '_thumbnail_id'
LEFT JOIN wp_blog_posts img_post ON thumb_meta.meta_value = img_post.ID
WHERE 
    tt.taxonomy = 'category' 
    AND tt.term_id = ${category.id}
    AND p.post_type = 'post'
    AND p.post_status = 'publish'
GROUP BY 
    p.ID, 
    p.post_title, 
    p.post_date,
    l.like_count,
    c.comment_count,
    a_stats.view_count,
    thumb_meta.meta_value,
    img_post.guid
ORDER BY p.post_date DESC
LIMIT 6
        `);

      groupedData.push({
        articles: posts.map(item => {
          let tagKeys = item.tags.split(',').map(Number);
          let tagsText = []
          tagKeys.forEach(t => {
            if (tagMap.has(t)) { tagsText.push(tagMap.get(t)) }
          })
          return { ...item, tags: tagsText }
        }),
        category: category.name
      })

    }
  }
  return { categories, groupedData };
};

// 路由
router.get('/posts', async (ctx) => {
  try {
    const data = await fetchWordPressData();
    ctx.body = successResponse(data);
  } catch (error) {
    ctx.status = 500;
    ctx.body = errorResponse('POSTS_FETCH_ERROR', '获取文章失败')
  }
});

// 获取文章详情
router.get('/article/:id', async (ctx) => {
  if (!ctx.params.id) {
    return ctx.body = errorResponse('ID_ERROR');
  }

  const id = ctx.params.id;
  try {
    const postSql = `
 			SELECT
    p.ID AS id,
    p.post_title AS title,
    p.post_content AS content,
    p.post_date_gmt AS date_gmt,
    p.post_modified_gmt AS modified_gmt,
    p.post_author AS author_id,
    u.user_login AS author_name,
    u.display_name AS author_display_name,
    pm1.meta_value AS featured_media_id,
    pm2.meta_value AS views,
    p.guid AS guid,
    p.post_parent AS post_parent,
    LEAST(
    ROUND(
        COALESCE(a_stats.view_count, 0) * 0.7 + 
        COALESCE(l.like_count, 0) * 0.5 + 
        COALESCE(c.comment_count, 0) * 0.3
    ) * 100,
    5000
) AS hot_score,
    COALESCE(l.like_count, 0) AS like_count,
    COALESCE(c.comment_count, 0) AS comment_count,
    (SELECT guid FROM wp_blog_posts WHERE ID = pm1.meta_value) AS featured_image_url
    FROM wp_blog_posts p
    LEFT JOIN wp_blog_users u ON p.post_author = u.ID
    LEFT JOIN wp_blog_postmeta pm1 
        ON p.ID = pm1.post_id 
        AND pm1.meta_key = '_thumbnail_id'
    LEFT JOIN wp_blog_postmeta pm2 
        ON p.ID = pm2.post_id 
        AND pm2.meta_key = 'views'
    LEFT JOIN articlestats a_stats 
        ON p.ID = a_stats.article_id
    LEFT JOIN (
        SELECT target_id, COUNT(*) AS like_count 
        FROM likes 
        WHERE target_type = 'posts'
        GROUP BY target_id
    ) l ON p.ID = l.target_id
    LEFT JOIN (
        SELECT article_id, COUNT(*) AS comment_count 
        FROM comments 
        WHERE is_deleted = 0
        GROUP BY article_id
    ) c ON p.ID = c.article_id
    WHERE p.ID = :id;
    `;

    const post = await sequelize.query(postSql, {
      replacements: { id },
      type: sequelize.QueryTypes.SELECT
    });

    if (post.length) {
      const guid = post[0].guid;
      const isImage = /\.(jpg|jpeg|png|webp|gif|bmp)(\?.*)?$/i.test(guid);
      post[0].jetpack_featured_media_url = isImage ? guid : null;
      ctx.body = successResponse(post[0]);
    } else {
      ctx.body = errorResponse(-1, '获取文章失败');
    }
  } catch (error) {
    console.error('获取文章失败:', error);
    ctx.status = 500;
    ctx.body = errorResponse(-1, '服务器内部错误');
  }
});

// 土味情话接口
router.get('/poquotessts', async (ctx) => {
  try {
    const loveList = await sequelize.query(`
      SELECT * 
      FROM wp_blog_posts 
      WHERE post_type = 'acf-field' AND post_status = 'publish'
      `);
    const quotes = loveList[0].map(post => {

      const data = unserialize(post.post_content); // 反序列化

      return data.default_value; // 提取 default_value
    });


    ctx.body = successResponse(quotes);
  } catch (error) {
    // 返回土味情话获取失败的错误信息
    ctx.body = errorResponse('QUOTES_FETCH_ERROR', '获取土味情话失败');
  }
});

export default router;